﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace AoYing_DeviceCollection.Model
{
    public class DataBaseHelper
    {
        MySqlConnection conn = null;
        [Obsolete]
        string connStr = ConfigurationManager.AppSettings["connStr"];
        public DataBaseHelper()
        {
            conn = new MySqlConnection(connStr);
        }

        public bool Insert(object entity, string tableName)
        {
            string sql = string.Empty;
            try
            {
                conn.Open();
                sql = EntityHelper.GetInsertSQL(entity, tableName);
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    var affectRows = cmd.ExecuteNonQuery();
                    if (affectRows == -1)
                    {
                        return false;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                BaseUtils._eventMsg.ErrorMsg = $"[插入出错]语句[{sql}]{ex.Message}\n {ex.StackTrace}";
                conn = null;
                return false;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn = null;
                }
            }
        }

        /// <summary>
        /// 插入sql
        /// </summary>
        /// <param name="insertSql"></param>
        /// <returns></returns>
        public bool Insert(string insertSql)
        {
            try
            {
                conn.Open();
                using (var cmd = new MySqlCommand(insertSql, conn))
                {
                    var affectRows = cmd.ExecuteNonQuery();
                    if (affectRows == -1)
                    {
                        return false;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                new LogHelper().Info($"数据库插入出错【{insertSql}】\r{ex.Message}");
                BaseUtils._eventMsg.ErrorMsg = $"[插入出错]语句[{insertSql}]{ex.Message}\n{ex.StackTrace}";
                conn = null;
                return false;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 更新sql
        /// </summary>
        /// <param name="updateSql"></param>
        /// <returns></returns>
        public int Update(string updateSql)
        {
            try
            {
                conn.Open();
                using (var cmd = new MySqlCommand(updateSql, conn))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                new LogHelper().Info($"数据库更新出错【{updateSql}】\r{ex.Message}");
                BaseUtils._eventMsg.ErrorMsg = $"[更新出错]{ex.Message}\n{ex.StackTrace}";
                conn = null;
                return 0;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn = null;
                }
            }
        }

        public DataTable GetDataTable(string selectSQL)
        {
            try
            {
                conn.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(selectSQL, conn);
                var dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
              
                new LogHelper().Info($"数据库查询出错【{selectSQL}】\r{ex.Message}");
                BaseUtils._eventMsg.ErrorMsg = $"[查表出错]{ex.Message}\n{ex.StackTrace}";

                conn.Close();
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return new DataTable();
        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="insertSql"></param>
        /// <returns></returns>
        public bool BatchInsert<T>(List<T> list, string tableName) where T : class, new()
        {
            try
            {
                conn.Open();
                string sql = string.Empty;
                bool isFirst = true;

                for (int i = 0; i < list.Count; i++)
                {
                    EntityHelper.GetBatchInsertSQL(list[i], isFirst, ref sql, tableName);
                    isFirst = false;
                }

                using (var cmd = new MySqlCommand(sql, conn))
                {
                    var affectRows = cmd.ExecuteNonQuery();
                    if (affectRows == -1)
                    {
                        return false;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                BaseUtils._eventMsg.ErrorMsg = $"[插入出错]{ex.Message}\n{ex.StackTrace}";
                return false;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn = null;
                }
            }
        }
    }

    public class EntityHelper
    {

        /// <summary>
        /// 获取插入sql
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static string GetInsertSQL(object entity, string tableName)
        {
            Type t = entity.GetType();
            PropertyInfo[] propertyInfo = t.GetProperties();

            if (propertyInfo.Count() > 1)
            {
                var sql = "insert into " + tableName;  //定义好类名即数据表名
                var fieldList = string.Empty;
                var fieldVals = string.Empty;
                foreach (var field in propertyInfo)
                {
                    if (field.Name.CompareTo("Id") == 0)
                    {
                        continue;
                    }
                    switch (field.PropertyType.Name)
                    {
                        case "String":
                            fieldList += field.Name + ",";
                            fieldVals += $"'{field.GetValue(entity, null)}',";
                            break;
                        case "Int32":
                            fieldList += field.Name + ",";
                            fieldVals += field.GetValue(entity, null) + ",";
                            break;
                        case "Double":
                            fieldList += field.Name + ",";
                            fieldVals += field.GetValue(entity, null) + ",";
                            break;
                        case "Boolean":
                            fieldList += field.Name + ",";
                            fieldVals += ((bool)field.GetValue(entity, null) ? 1 : 0) + ",";
                            break;
                        case "DateTime":
                            fieldList += field.Name + ",";
                            fieldVals += $"'{field.GetValue(entity, null)}',";
                            break;
                        default:
                            break;
                    }
                }
                fieldList = fieldList.Remove(fieldList.Length - 1);
                fieldVals = fieldVals.Remove(fieldVals.Length - 1);
                sql += "(" + fieldList + ")" + "values" + "(" + fieldVals + ");";
                return sql;
            }

            return string.Empty;
        }


        /// <summary>
        /// 获取实体插入sql,支持生产批量插入sql
        /// </summary>
        /// <param name="entity">对象</param>
        /// <param name="isNeed">是否需要表名</param>
        /// <param name="sql">返回sql</param>
        /// <param name="tableName">自定义表名</param>
        public static void GetBatchInsertSQL(object entity, bool isNeed, ref string sql, string tableName)
        {
            Type t = entity.GetType();
            PropertyInfo[] propertyInfo = t.GetProperties();

            if (propertyInfo.Count() > 1)
            {
                var fieldList = string.Empty;
                var fieldVals = string.Empty;
                foreach (var field in propertyInfo)
                {
                    if (field.Name.CompareTo("Id") == 0)
                    {
                        continue;
                    }
                    if (isNeed) //是否是第一个values(...,...,...,..)
                    {
                        switch (field.PropertyType.Name)
                        {
                            case "String":
                                fieldList += $"{tableName}.{field.Name}" + ",";
                                break;
                            case "Int32":
                                fieldList += field.Name + ",";
                                break;
                            case "Double":
                                fieldList += field.Name + ",";
                                break;
                            case "Boolean":
                                fieldList += field.Name + ",";
                                break;
                            case "DateTime":
                                fieldList += field.Name + ",";
                                break;
                            default:
                                break;
                        }
                    }
                    switch (field.PropertyType.Name)
                    {
                        case "String":
                            fieldVals += $"'{field.GetValue(entity, null)}',";
                            break;
                        case "Int32":
                            fieldVals += field.GetValue(entity, null) + ",";
                            break;
                        case "Double":
                            fieldVals += field.GetValue(entity, null) + ",";
                            break;
                        case "Boolean":
                            fieldVals += ((bool)field.GetValue(entity, null) ? 1 : 0) + ",";
                            break;
                        case "DateTime":
                            fieldVals += $"'{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'" + ",";
                            break;
                        default:
                            break;
                    }
                }
                fieldVals = fieldVals.Remove(fieldVals.Length - 1);
                if (isNeed)
                {
                    fieldList = fieldList.Remove(fieldList.Length - 1);
                    sql += "insert into " + tableName + "(" + fieldList + ")" + "values" + "(" + fieldVals + ")";
                }
                else
                {
                    sql += "," + "(" + fieldVals + ")";
                }
            }
        }
    }
}
